import json
import re
import psycopg2
import configparser
import os
from pathlib import Path
from typing import List, Tuple, Dict, Any, Optional


def get_table_image_info(table_name: str) -> Optional[Dict[str, Any]]:
    """
    从table_images表中查询表格图片信息
    
    Args:
        table_name: 表格名称
        
    Returns:
        包含表格图片信息的字典，如果未找到则返回None
    """
    print(f"   [DEBUG] get_table_image_info 被调用，table_name='{table_name}'")
    try:
        # 查找配置文件 - 使用 table 目录下的配置
        project_root = Path(__file__).parent.parent.parent
        config_path = project_root / 'table' / 'db_config.ini'
        
        print(f"   [DEBUG] 配置文件路径: {config_path}")
        print(f"   [DEBUG] 配置文件存在: {config_path.exists()}")
        
        if not config_path.exists():
            print(f"   [DEBUG] 配置文件不存在，返回None")
            return None
        
        config = configparser.ConfigParser()
        config.read(str(config_path), encoding='utf-8')
        
        # 连接到QA_knowledge数据库
        print(f"   [DEBUG] 尝试连接数据库...")
        conn = psycopg2.connect(
            host=config['postgresql']['HOST'],
            port=config['postgresql']['PORT'],
            user=config['postgresql']['USER_NAME'],
            password=config['postgresql']['PASSWD'],
            database='QA_knowledge'
        )
        print(f"   [DEBUG] 数据库连接成功")
        
        try:
            with conn.cursor() as cursor:
                print(f"   [DEBUG] 执行查询...")
                cursor.execute("""
                    SELECT table_name, table_title, table_description, images
                    FROM table_images
                    WHERE table_name = %s
                """, (table_name,))
                
                result = cursor.fetchone()
                print(f"   [DEBUG] 查询结果: {result is not None}")
                if result:
                    print(f"   [DEBUG] 返回数据: table_name={result[0]}, images类型={type(result[3])}")
                    return {
                        "table_name": result[0],
                        "table_title": result[1],
                        "table_description": result[2],
                        "images": result[3]  # 现在是JSONB数组
                    }
        finally:
            conn.close()
            
    except Exception as e:
        print(f"   [DEBUG] 查询表格图片信息失败: {e}")
        import traceback
        traceback.print_exc()
    
    print(f"   [DEBUG] 返回None")
    return None


def extract_table_name_from_context(context: str) -> Optional[str]:
    """
    从context字段中提取table_name
    
    Args:
        context: 上下文字符串
        
    Returns:
        提取到的表格名称，如果未找到则返回None
    """
    if not context:
        return None
    
    try:
        # 尝试解析为JSON
        data = json.loads(context)
        if isinstance(data, dict) and 'table_name' in data:
            return data['table_name']
    except:
        # 如果不是JSON，尝试用正则表达式匹配
        match = re.search(r'table_name[\'\":\s]+([^\'\",}\s]+)', context)
        if match:
            return match.group(1)
    
    return None


def normalize_context(text: str) -> str:
    """
    标准化上下文用于去重：
    - 尝试解析为 JSON 并移除 row_data/rowData 字段后再序列化
    - 若非 JSON，则进行轻量字符串清洗：去空白、去标点符号、转小写
    """
    if not isinstance(text, str):
        text = str(text)

    try:
        data = json.loads(text)
        if isinstance(data, dict):
            data.pop("row_data", None)
            data.pop("rowData", None)
        return json.dumps(data, ensure_ascii=False, separators=(",", ":"))
    except Exception:
        pass

    core = re.sub(r"\s+", "", text)
    core = re.sub(r"[^\w\u4e00-\u9fff]", "", core)
    return core.lower()


def build_llm_context_from_qa(results: List[Tuple[Any, Any, Any, Any, Any, Any]]) -> Tuple[str, List[Dict[str, Any]]]:
    """
    根据查询结果构造传给大模型的参考信息，并返回：
    - context_for_llm: 包含 Q&A 列表和去重后的上下文
    - qa_results: 结构化的 Q&A 结果列表，便于后续存储或展示

    results 元素预期形态：
    (question, question_tokens, answer_text, cat_id, context_text, match_count)
    """
    qa_prompt_parts: List[str] = []
    unique_contexts: List[str] = []
    seen_context_keys = set()
    qa_results: List[Dict[str, Any]] = []

    for idx, res in enumerate(results, start=1):
        try:
            question, question_tokens, answer_text, cat_id, context_text, match_count = res
        except Exception:
            # 兼容不同形态的元组
            question = res[0] if len(res) > 0 else ""
            answer_text = res[2] if len(res) > 2 else ""
            context_text = res[4] if len(res) > 4 else ""
            match_count = res[5] if len(res) > 5 else None
            question_tokens = res[1] if len(res) > 1 else None
            cat_id = res[3] if len(res) > 3 else None

        # 检查是否包含表格信息
        table_name = extract_table_name_from_context(str(context_text)) if context_text else None
        table_info = None
        is_table_question = False
        
        print(f"🔍 检查问题 {idx}: {question[:50]}...")
        if context_text:
            print(f"   Context前100字: {str(context_text)[:100]}...")
        print(f"   提取到的table_name: {table_name}")
        
        if table_name:
            table_info = get_table_image_info(table_name)
            print(f"   查询table_images结果: {'找到' if table_info else '未找到'}")
            if table_info:
                is_table_question = True
                print(f"✅ 发现表格问题: {question[:30]}... -> 表格: {table_name}")
                print(f"   table_info: {list(table_info.keys()) if table_info else None}")

        # 收集 Q&A 显示内容（所有问题都加入LLM上下文，包括表格问题）
        qa_prompt_parts.append(f"问题 {idx}: {question}")
        qa_prompt_parts.append(f"答案 {idx}: {answer_text}")

        # 去重上下文（忽略 row_data）
        ctx_key = normalize_context(str(context_text))
        if ctx_key and ctx_key not in seen_context_keys:
            seen_context_keys.add(ctx_key)
            unique_contexts.append(str(context_text))

        qa_result = {
            "index": idx,
            "question": question,
            "answer": answer_text,
            "match_count": match_count,
            "category_id": cat_id,
            "context": context_text,
            "is_table": is_table_question,
        }
        
        # 如果是表格问题，添加表格信息
        if is_table_question and table_info:
            qa_result["table_info"] = table_info
        
        qa_results.append(qa_result)

    dedup_context_text = "\n".join([f"上下文: {c}" for c in unique_contexts]) if unique_contexts else ""
    context_for_llm = (
        "\n".join(qa_prompt_parts) + (f"\n\n{dedup_context_text}" if dedup_context_text else "")
    )

    return context_for_llm, qa_results